/*
 * Copyright © OpenAtom Foundation.
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *     http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 */

package io.iec.edp.caf.databaseobject.sqlprovider;

import io.iec.edp.caf.databaseobject.DataTypeConverter;
import io.iec.edp.caf.databaseobject.api.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.engine.config.spi.ConfigurationService;
import org.hibernate.resource.transaction.spi.DdlTransactionIsolator;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.hibernate.tool.schema.extract.spi.DatabaseInformation;
import org.hibernate.tool.schema.extract.spi.NameSpaceTablesInformation;
import org.hibernate.tool.schema.extract.spi.TableInformation;
import org.hibernate.tool.schema.internal.ExceptionHandlerHaltImpl;
import org.hibernate.tool.schema.internal.Helper;
import org.hibernate.tool.schema.internal.HibernateSchemaManagementTool;
import org.hibernate.tool.schema.internal.exec.JdbcContext;
import org.hibernate.tool.schema.spi.ExecutionOptions;
import org.hibernate.tool.schema.spi.SchemaManagementTool;
import org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 * SQL提供器
 *
 * @author renmh
 */
@Slf4j
public class SqlProvider {

    private NameSpaceTablesInformation tables = null;

    /**
     * 获取列操作的sql语句集合
     *
     * @param table               表名
     * @param databaseObjectTable 数据库对象
     * @param operation           列操作
     * @param db                  数据库信息
     * @return sql l列表
     */
    public List<String> providerSql(String table, DatabaseObjectTable databaseObjectTable, List<String> languages, ColumnChangeOperation operation, DBInfo db, StandardServiceRegistryBuilder serviceRegistryBuilder, Metadata metadata) {
        if (!StringUtils.hasText(table) || operation == null || db == null) {
            return new ArrayList<>();
        }
        List<String> totalSqls = new ArrayList<>();
        List<String> sqls = getSqls(table, databaseObjectTable, languages, operation.getColumn(), operation, db, serviceRegistryBuilder, metadata);
        if (sqls.size() > 0) {
            totalSqls.addAll(sqls);
        }
        return totalSqls;
    }

    private List<String> getSqls(String table, DatabaseObjectTable databaseObjectTable, List<String> languages, DatabaseObjectColumn column, ColumnChangeOperation operation, DBInfo db, StandardServiceRegistryBuilder serviceRegistryBuilder, Metadata metadata) {
        List<String> sqls = new ArrayList<>();
        DataType columnType = column.getDataType();
        if (!isSupportedColumnType(columnType)) {
            return sqls;
        }
        DataType targetColumnType = getTargetColumnType(operation, column.getDataType(), db);
        boolean isColumnTypeChanged = targetColumnType != column.getDataType();
        String targetColumnLength = getTargetColumnLength(operation);
        boolean isColumnLengthChanged = targetColumnLength != null;
        String targetColumnDefaultValue = getTargetColumnDefaultValue(operation);
        boolean isColumnDefaultValueChanged = targetColumnDefaultValue != null;
        sqls = generateSqls(table, databaseObjectTable, languages, column, db, isColumnTypeChanged, targetColumnType, isColumnLengthChanged, targetColumnLength, isColumnDefaultValueChanged, targetColumnDefaultValue, serviceRegistryBuilder, metadata);
        return sqls;
    }

    /**
     * 是否是支持修改列类型、长度的的字段类型
     *
     * @param columnType
     * @return
     */
    private boolean isSupportedColumnType(DataType columnType) {
        if (columnType == DataType.Varchar || columnType == DataType.Char || columnType == DataType.Decimal || columnType == DataType.SmallInt
                || columnType == DataType.NChar || columnType == DataType.NVarchar || columnType == DataType.Int || columnType == DataType.DateTime) {
            return true;
        }
        return false;
    }

    /**
     * 获取列需要转换到的字段类型
     *
     * @param columnChangeOperation 列的修改操作
     * @param columnType            列的原始字段类型
     * @param db                    数据库信息
     * @return 要转换到的字段类型
     */
    private DataType getTargetColumnType(ColumnChangeOperation columnChangeOperation, DataType columnType, DBInfo db) {
        DataType targetColumnType = columnType;
        for (ColumnOperation operation : columnChangeOperation.getOperations()) {
            if (operation.getOperationType() == 1) {
                //目前支持的类型转换:varchar>>nvarchar，smallint>>int,
                DataType newColumnType = (DataType) operation.getNewValue();
                DataType oldColumnType = (DataType) operation.getOldValue();
                if ((oldColumnType == DataType.SmallInt && newColumnType == DataType.Int) ||
                        (oldColumnType == DataType.Varchar && newColumnType == DataType.NVarchar) ||
                        (oldColumnType == DataType.Char && newColumnType == DataType.NChar) ||
                        (oldColumnType == DataType.Char && newColumnType == DataType.Varchar) ||
                        (oldColumnType == DataType.Int && newColumnType == DataType.Decimal) ||
                        (oldColumnType == DataType.DateTime && newColumnType == DataType.TimeStamp)) {
                    targetColumnType = newColumnType;
                }
                break;
            }
        }
        return targetColumnType;
    }

    private String getTargetColumnLength(ColumnChangeOperation columnChangeOperation) {
        for (ColumnOperation operation : columnChangeOperation.getOperations()) {
            if (operation.getOperationType() == 2) {
                return String.valueOf(operation.getNewValue());
            }
        }
        return null;
    }

    private String getTargetColumnDefaultValue(ColumnChangeOperation columnChangeOperation) {
        for (ColumnOperation operation : columnChangeOperation.getOperations()) {
            if (operation.getOperationType() == 3) {
                return String.valueOf(operation.getNewValue());
            }
        }
        return null;
    }

    private String getDataTypeStr(DataType dataType, DbType dbType) {
        String type = "";
        String dataTypeStr = String.valueOf(dataType);
        if (DataTypeConverter.getInstance().IsExistConverMapping(dataTypeStr)) {
            type = DataTypeConverter.getInstance().GetDataType(dataTypeStr, dbType.toString());
        } else if (dataType == DataType.LongInt) {
            if (dbType == DbType.Oracle) {
                type = "number(20)";
            } else {
                type = "bigint";
            }
        } else if (dataType == DataType.SmallInt) {
            type = "smallint";
        } else if (dataType == DataType.Varchar) {
            if (dbType == DbType.DM || dbType == DbType.Oracle) {
                type = "varchar2";
            } else {
                type = dataTypeStr;
            }
        } else if (dataType == DataType.Decimal) {
            if (dbType == DbType.Oracle) {
                type = "number";
            } else {
                type = dataTypeStr;
            }
        } else {
            type = dataTypeStr;
        }
        return type;
    }

    private String getColumnTypeAndLenthStr(String dataType, String length, DbType dbType) {
        String columnTypeAndLenthStr = null;
        if (dbType == DbType.Oracle && ("varchar2".equalsIgnoreCase(dataType) || "char".equalsIgnoreCase(dataType)) && Integer.parseInt(length) > 4000) {

            length = "4000";
        }
        if (dbType == DbType.Oracle && ("nvarchar2".equalsIgnoreCase(dataType) || "nchar".equalsIgnoreCase(dataType)) && Integer.parseInt(length) > 2000) {
            length = "2000";
        }
        if (dataType.equalsIgnoreCase("smallint") || dataType.equalsIgnoreCase("int") || dataType.equalsIgnoreCase("timestamp") ||
                dataType.equalsIgnoreCase("datetime2") || dataType.equalsIgnoreCase("datetime(3)")) {
            columnTypeAndLenthStr = dataType;
        } else {
            columnTypeAndLenthStr = dataType + "(" + length + ")";
        }
        return columnTypeAndLenthStr;
    }

    private String formartDefaultValue(String dataType, String defaultValue, DbType dbType) {
        String columnDefaultValue = null;
        if (dataType.equalsIgnoreCase("smallint") || dataType.equalsIgnoreCase("int") || dataType.equalsIgnoreCase("decimal")) {
            columnDefaultValue = defaultValue;
            if(dbType == DbType.DB2)
            {
                columnDefaultValue = "'" + defaultValue + "'";
            }
        } else {
            columnDefaultValue = "'" + defaultValue + "'";
            if (dbType == DbType.Oracle || dbType == DbType.DM || dbType == DbType.SQLServer) {
                columnDefaultValue = "N'" + defaultValue + "'";
            }
        }
        return columnDefaultValue;
    }

    private List<String> generateSqls(String table, DatabaseObjectTable databaseObjectTable, List<String> languages, DatabaseObjectColumn column, DBInfo db, boolean isColTypeChanged, DataType newColType, boolean isColLenChanged, String targetColLen, boolean isColDefaultValueChanged, String targetColDefaultValue, StandardServiceRegistryBuilder serviceRegistryBuilder, Metadata metadata) {
        String sql = null;
        List<String> sqls = new ArrayList<>();
        List<String> columns = getMulityLanguageColumns(databaseObjectTable, column, languages);
        boolean isMulityLanguage = columns.size() > 1;
        //列类型未改，默认值未改，长度修改，但长度小于数据库列的长度
        boolean isNeedChangeColLen = (isColLenChanged && !isMulityLanguage && canChangeLength(table, column, newColType, targetColLen, serviceRegistryBuilder, metadata)) || (isColLenChanged && isMulityLanguage);
        if (!isColTypeChanged && !isColDefaultValueChanged && !isNeedChangeColLen) {
            return sqls;
        }
        //列的长度
        String columnFinalLength = null;
        if (isNeedChangeColLen) {
            columnFinalLength = targetColLen;
        } else {
            columnFinalLength = String.valueOf(column.getLength());
            if (newColType == DataType.Decimal) {
                if(db.getDbType() == DbType.DB2 && (column.getPrecision() > 31))
                {
                    column.setPrecision(31);
                }
                columnFinalLength = column.getPrecision() + "," + column.getScale();
            }
        }
        String targetColType = getDataTypeStr(newColType, db.getDbType());
        //类型+长度的最终组合，比如varchar2(36)  decimal(18,2)  int
        String columnTypeAndLenthStr = getColumnTypeAndLenthStr(targetColType, columnFinalLength, db.getDbType());
        //默认值
        String defaultValueStr = column.getDefaultValue();
        if (isColDefaultValueChanged) {
            defaultValueStr = targetColDefaultValue;
        }
        for (String colunmName : columns) {
            //构造Sql
            if (db.getDbType() == DbType.PgSQL || db.getDbType() == DbType.HighGo || db.getDbType() == DbType.Kingbase || db.getDbType() == DbType.OpenGauss) {
                //alter table 表名 alter column 列名 数据类型
                sql = "alter table " + table + " alter column " + colunmName + " type " + columnTypeAndLenthStr;
                sqls.add(sql);
                if (!column.isNullable() && !isMulityLanguage) {
                    sql = "ALTER TABLE " + table + " ALTER COLUMN " + colunmName + " SET NOT NULL";
                    sqls.add(sql);
                }
                if (StringUtils.hasText(defaultValueStr)) {
                    sql = "ALTER TABLE " + table + " ALTER COLUMN " + colunmName + " SET DEFAULT " + formartDefaultValue(targetColType, defaultValueStr, db.getDbType());
                    sqls.add(sql);
                }
            }
            if (db.getDbType() == DbType.Oracle || db.getDbType() == DbType.DM || db.getDbType() == DbType.Oscar) {
                //alter table 表名 modify 列名 数据类型
                String colName = colunmName;
                if (db.getDbType() == DbType.DM) {
                    colName = "\"" + colunmName.toUpperCase() + "\"";
                }
                sql = "alter table " + table + " modify " + colName + " " + columnTypeAndLenthStr;
                if (StringUtils.hasText(defaultValueStr)) {
                    sql = "alter table " + table + " modify " + colName + " " + columnTypeAndLenthStr + " DEFAULT " + formartDefaultValue(targetColType, defaultValueStr, db.getDbType());
                }
                sqls.add(sql);
            }
            if (db.getDbType() == DbType.SQLServer) {
                //ALTER TABLE table ALTER COLUMN test NVARCHAR(50)
                //去掉默认值约束
                sql = "DECLARE @var0 sysname;\n" +
                        "SELECT @var0 = [d].[name]\n" +
                        "FROM [sys].[default_constraints] [d]\n" +
                        "INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]\n" +
                        "WHERE ([d].[parent_object_id] = OBJECT_ID(N'[" + table + "]') AND [c].[name] = N'" + colunmName + "');\n" +
                        "IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [" + table + "] DROP CONSTRAINT [' + @var0 + '];')";
                sqls.add(sql);
                sql = "alter table " + table + " alter column " + colunmName + " " + columnTypeAndLenthStr;
                if (!column.isNullable() && !isMulityLanguage) {
                    sql = "alter table " + table + " alter column " + colunmName + " " + columnTypeAndLenthStr + " not null";
                }
                sqls.add(sql);
                if (StringUtils.hasText(defaultValueStr)) {
                    sql = "ALTER TABLE " + table + " ADD DEFAULT " + formartDefaultValue(targetColType, defaultValueStr, db.getDbType()) + " FOR " + colunmName;
                    sqls.add(sql);
                }
            }
            if (db.getDbType() == DbType.MySQL) {
                //MySQL默认字段名小写，否则修改长度会把字段大小写也改变
                sql = "alter table " + table + " modify column " + colunmName.toLowerCase() + " " + columnTypeAndLenthStr;
                if (!column.isNullable() && !isMulityLanguage) {
                    sql = "alter table " + table + " modify column " + colunmName.toLowerCase() + " " + columnTypeAndLenthStr + " NOT NULL";
                }
                sqls.add(sql);
                if (StringUtils.hasText(defaultValueStr)) {
                    String defaultSql = "ALTER TABLE " + table + " ALTER COLUMN " + colunmName.toLowerCase() + " SET DEFAULT " + formartDefaultValue(targetColType, defaultValueStr, db.getDbType());
                    sqls.add(defaultSql);
                }
            }
            if (db.getDbType() == DbType.DB2) {
                sql = "alter table " + table + " alter column " + colunmName + " set data type " + columnTypeAndLenthStr;

                sqls.add(sql);
                if (StringUtils.hasText(defaultValueStr)) {
                    String defaultSql = "ALTER TABLE " + table + " ALTER COLUMN " + colunmName + " SET DEFAULT " + formartDefaultValue(targetColType, defaultValueStr, db.getDbType());
                    sqls.add(defaultSql);
                }
                //DB2每次alter表，必须执行REORG TABLE
                String reorgSql = "CALL SYSPROC.ADMIN_CMD('REORG TABLE " + table + "')";
                sqls.add(reorgSql);
            }
        }
        return sqls;
    }

    private boolean canChangeLength(String table, DatabaseObjectColumn column, DataType newColType, String colLength, StandardServiceRegistryBuilder serviceRegistryBuilder, Metadata metadata) {
        //类型转换 由int转换为Decimal时，不需要判断长度
        if (column.getDataType() == DataType.Int && newColType == DataType.Decimal) {
            return true;
        }
        if (tables == null) {
            ServiceRegistry serviceRegistry = serviceRegistryBuilder.build();
            SchemaManagementTool smt = serviceRegistry.getService(SchemaManagementTool.class);
            ((HibernateSchemaManagementTool) smt).injectServices((ServiceRegistryImplementor) serviceRegistry);

            HibernateSchemaManagementTool tool = (HibernateSchemaManagementTool) smt;

            Map config = new HashMap();
            config.putAll(serviceRegistry.getService(ConfigurationService.class).getSettings());
            final ExecutionOptions executionOptions = SchemaManagementToolCoordinator.buildExecutionOptions(
                    config,
                    ExceptionHandlerHaltImpl.INSTANCE
            );

            final JdbcContext jdbcContext = tool.resolveJdbcContext(executionOptions.getConfigurationValues());

            final DdlTransactionIsolator isolator = tool.getDdlTransactionIsolator(jdbcContext);

            final DatabaseInformation databaseInformation = Helper.buildDatabaseInformation(
                    serviceRegistry,
                    isolator,
                    metadata.getDatabase().getDefaultNamespace().getName()
            );
            tables = databaseInformation.getTablesInformation(metadata.getDatabase().getDefaultNamespace());
        }

        //不同数据库表名策略不一致
        TableInformation dbTable = tables.getTableInformation(table);
        if (dbTable == null) {
            dbTable = tables.getTableInformation(table.toLowerCase());
        }
        if (dbTable == null) {
            dbTable = tables.getTableInformation(table.toUpperCase());
        }
        if (dbTable == null) {
            throw new RuntimeException("检测到增长表字段长度操作，但是对应表不存在，请确认！表名称为：" + table);
        }
        Identifier columnIdentifier = Identifier.toIdentifier(column.getCode());
        int oldColumnSize = 0;
        if (newColType == DataType.Decimal) {
            //oldColumnSize = Integer.parseInt(operation.getNewValue().toString().split(",")[0]) - Integer.parseInt(operation.getNewValue().toString().split(",")[1]);
            oldColumnSize = Integer.parseInt(colLength.split(",")[0]);
        } else {
            oldColumnSize = Integer.parseInt(colLength);
        }
        if (dbTable.getColumn(columnIdentifier).getColumnSize() > oldColumnSize) {
            log.warn("检测数据库中表字段长度大于DBO中字段长度，已忽略！" + "当前表名为" + table + ";字段编号为" + column.getCode() + ",数据库中字段长度为" + dbTable.getColumn(columnIdentifier).getColumnSize() + ",DBO中字段长度为" + oldColumnSize);
            return false;
        }
        return true;
    }

    private List<String> getMulityLanguageColumns(DatabaseObjectTable databaseObjectTable, DatabaseObjectColumn column, List<String> languages) {
        List<String> columns = new ArrayList<>();
        if (databaseObjectTable.isI18NObject() && databaseObjectTable.getMultiLanguageColumns() != null && databaseObjectTable.getMultiLanguageColumns().size() > 0) {
            //判断是否多语列，通过列id和code双重判断
            Map<String, String> languageColumnCodes = databaseObjectTable.getMultiLanguageColumnCodes();
            List<String> languageCodeList = new ArrayList<>(languageColumnCodes.values());
            List<String> languageColumns = new ArrayList<>();
            //去掉列的多语后缀，比如name_chs，去掉后为name
            String columnName = null;
            for (String languageCode : languageCodeList) {
                columnName = languageCode;
                for (String language : languages) {
                    if (columnName.endsWith(language)) {
                        columnName = columnName.substring(0, columnName.length() - language.length());
                        break;
                    }
                }
                languageColumns.add(columnName);
            }
            if (databaseObjectTable.getMultiLanguageColumns().contains(column.getId()) || languageColumns.contains(column.getCode())) {
                for (String language : languages) {
                    columns.add(column.getCode() + language);
                }
            } else {
                columns.add(column.getCode());
            }
        } else {
            columns.add(column.getCode());
        }
        return columns;
    }
}
